package com.qq.financeSystem.mapper;

import com.qq.financeSystem.model.*;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.security.PublicKey;
import java.util.List;

@Mapper
@Repository
public interface SendSalaryMapper {

    //查询本月人员底薪（根据识别码）
    @Select("SELECT name as emp_name,gender,employeeId,post,dept,salary,identificationCode" +
            " FROM employee_information WHERE identificationCode=#{identificationCode}")
    public List<EmpSalary> selectAllEmpSalary(String identificationCode);

    //查询本月个人车辆提成总额（ID）
    @Select("SELECT SUM(royalty)as commission FROM order_info where salesman_id = #{employeeId} " +
            "and DATE_FORMAT(selling_time,'%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )")
    public Double selectCarSumById(String employeeId);

    //查询本月个人修理提成总额（）
    @Select("SELECT SUM(push_money)as commission FROM car_maintain where job_num = #{employeeId} " +
            "and state=0 and DATE_FORMAT(over_time,'%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )")
    public Double selectServiceSumById(String employeeId);

    //查询指定历史部门和月份工资表ID（KEY）
    @Select("select * from sendsalary where year(sendsalary.times)=#{year} " +
            "and month(sendsalary.times)=#{month} and identificationCode=#{identificationCode}")
    public List<EmpSalary> selectAllEmpSalaryByKey(YearAndMonth key);

    //查询指定历史员工工资表（ID）
    @Select("select * from sendsalary where employeeId=#{employeeId} ORDER BY times")
    public List<EmpSalary> selectOneById(String employeeId);

    //结算工资(正式存入工资表)
    @Insert("insert into sendsalary values(null,#{empName},#{gender},#{employeeId}," +
            "#{post},#{dept},#{salary},#{commission},#{offsetMoney},#{sumMoney},#{times},#{identificationCode})")
    public int insertEmpSalary(EmpSalary empSalary);

    //查询指定人员是否本月已结算
    @Select("select employeeId from sendSalary where employeeId=#{employeeId} " +
            "and DATE_FORMAT(times,'%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )")
    public EmpSalary selectHasOneById(String employeeId);

    //查询指定销售人员ID基本薪资+提成信息（补贴除外）
    @Select("SELECT name as emp_name,gender,employeeId,post,dept,salary,SUM(royalty) as commission," +
            "identificationCode FROM employee_information,order_info where employeeId=salesman_id " +
            "and employeeId=#{employeeId} and DATE_FORMAT(selling_time,'%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )")
    public EmpSalary selectCarOneInfo(String employeeId);

    //查询指定维修人员ID基本薪资+提成信息（补贴除外）
    @Select("SELECT name as emp_name,gender,employeeId,post,dept,salary,SUM(push_money) as commission," +
            "identificationCode FROM employee_information,car_maintain  where employeeId=job_num and state=0 " +
            "and employeeId=#{employeeId} and DATE_FORMAT(over_time,'%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )")
    public EmpSalary selectServiceOneInfo(String employeeId);

    //查询指定仓库人员ID基本薪资信息+补贴（有补贴，无提成）
    @Select("SELECT name as emp_name,gender,employeeId,post,dept,salary,SUM(offset_money) as offset_money," +
            "identificationCode FROM employee_information,offset where employeeId=emp_id and employeeId=#{employeeId} " +
            "and DATE_FORMAT(time,'%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )")
    public EmpSalary selectHomeOneInfo(String employeeId);

    //查询本月个人汽车详情提成信息（ID）
    @Select("SELECT order_id,brand,type,selling_price,selling_time,royalty FROM order_info where salesman_id=#{employeeId}" +
            "and DATE_FORMAT(selling_time,'%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )")
    public List<CarRoyalty> selectCarById(String employeeId);

    //查询本月个人修理详情提成信息（ID）
    @Select("SELECT car_number,plate_number,car_type,color,push_money,over_time from car_maintain where job_num=#{employeeId} " +
            "and state=0 and DATE_FORMAT(over_time,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m')")
    public List<ServiceMoney> selectServiceById(String employeeId);

    //查询指定年月份个人汽车详情提成信息（ID）
    @Select("SELECT order_id,brand,type,selling_price,selling_time,royalty FROM order_info where salesman_id=#{employeeId} " +
            "and year(order_info.selling_time)=#{year} and month(order_info.selling_time)=#{month}")
    public List<CarRoyalty> selectLastCarById(YearAndMonth key);

    //查询指定年月份个人修理详情提成信息（ID）
    @Select("SELECT car_number,plate_number,car_type,color,push_money,over_time from car_maintain where job_num=#{employeeId} " +
            "and state=0 and year(car_maintain.over_time)=#{year} and month(car_maintain.over_time)=#{month}")
    public List<ServiceMoney> selectLastServiceById(YearAndMonth key);

    //查询本月销售部门提成总额（财务分析调用）
    @Select("SELECT IFNULL(SUM(royalty),0)as royalty from order_info where DATE_FORMAT(selling_time,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m')")
    public BigDecimal selectMonthAllRoyalty();

    //查询本月售后部门提成总额（财务分析调用）
    @Select("SELECT IFNULL(SUM(push_money),0)as royalty from car_maintain where DATE_FORMAT(over_time,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m')")
    public BigDecimal selectMonthAllPushMoney();

    //查询某部门底薪总额（财务分析调用）
    @Select("select dept,count(employeeId)as person,IFNULL(SUM(salary),0)as salary from employee_information where identificationCode = #{ident}")
    public MonthSalaryMoney selectSumSalaryMonthByIdent(String ident);

    //查询本月仓库进货详情（车辆,财务分析调用）
    @Select("SELECT car_name as brand,car_version as type,car_total as count,car_inprice as price,car_allprice as sumPrice from car " +
            "where DATE_FORMAT(car_indate,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m')")
    public List<MonthHomeMoney> selectMonthOutCarHome();

    //查询本月仓库进货详情（配件，财务分析调用）
    @Select("SELECT part_name as brand,part_version as type,part_total as count,part_inprice as price,part_allprice as sumPrice from part " +
            "where DATE_FORMAT(part_indate,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m')")
    public List<MonthHomeMoney> selectMonthOutPartsHome();

    //查询某月各部门薪资支出详情（部门+人数+底薪+提成+补贴，财务分析调用）
    @Select("SELECT dept,COUNT(employeeId)as person,SUM(salary)as salary,SUM(commission)as royalty,SUM(offset_money)as offsetMoney from sendsalary " +
            "where identificationCode=#{identificationCode} and year(sendsalary.times)=#{year} and month(sendsalary.times)=#{month}\n")
    public MonthSalaryMoney selectOutYearToMonthSalaryByKey(YearAndMonth key);

    //查询指定月份仓库进货详情（车辆，财务分析调用）
    @Select("SELECT car_name as brand,car_version as type,car_total as count,car_inprice as price,car_allprice as sumPrice " +
            "from car where year(car_indate)=#{year} and month(car_indate)=#{month}")
    public List<MonthHomeMoney> selectOutYearToMonthCarByKey(YearAndMonth key);

    //查询指定月份仓库进货详情（配件，财务分析调用）
    @Select("SELECT part_name as brand,part_version as type,part_total as count,part_inprice as price,part_allprice as sumPrice " +
            "from part where year(part_indate)=#{year} and month(part_indate)=#{month}")
    public List<MonthHomeMoney> selectOutYearToMonthPartByKey(YearAndMonth key);
}
